Grokking Database Fundamentals for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction to Database Systems
Course Overview
Overview of Databases
Core Components of Database
Database Architectures
Relational Vs. Non-relational Databases
Data Storage Fundamentals
File Organization in DBMS
Data Compression and Encoding
Quiz
Introduction to Database Indexes
Single Level Indexing
Tree-Based Indexing Techniques
Hash-Based Indexing
Bloom Filters
Quiz
Understanding the Transaction
Atomicity
Consistency
Isolation
Durability
Quiz
Understanding the Concurrency Control
Optimistic vs. Pessimistic Concurrency Control
Lock-Based Concurrency Control
Timestamp-Based Concurrency Control
Multiversion Concurrency Control (MVCC)
Quiz
Fundamentals of Distributed Databases
Partitioning in Databases
Partitioning Types
Sharding in Databases
Distributed Query Processing
Quiz
Replication in Databases
Replication Topologies
Understanding the Consistency Models in Databases
The CAP Theorem and Its Implications
Quiz
Understanding the Consensus
Consensus algorithms
Leader Election Strategies
Quiz
Understanding the Fault Tolerance
Logging Mechanisms in Databases
Checkpointing and Recovery Basics
Coordinated Recovery in Distributed Systems
High Availability Through Replication and Failover
Ensuring Data Resilience: Redundancy, Mirroring, and Recovery
Quiz
Introduction to NoSQL
Key-Value Stores
Document-Oriented Databases
Column-Family and Wide-Column Stores
Graph Databases
Quiz
Quora System Design & Database Design
TikTok System Design & Database Design
Airbnb System Design & Database Design
Snapchat System Design & Database Design
Core Components of Database

Databases rely on several interconnected components to function effectively. These components work together to store, manage, retrieve, and secure data while ensuring efficient performance and reliability. Understanding these components is vital for database engineers to design and optimize robust systems.

Core Functional Components

Image

1. Query Processor

The query processor is responsible for interpreting and executing user queries, translating them into operations the database can perform.

Key Subcomponents

Image
  1. Query Parser:
    • Checks the syntax and semantics of queries.
    • Converts high-level SQL statements into an internal representation.
  2. Query Optimizer:
    • Evaluates multiple ways to execute a query.
    • Chooses the most efficient execution plan based on cost estimation.
  3. Execution Engine:
    • Executes the query plan generated by the optimizer.
    • Interacts with the storage engine to retrieve or modify data.

2. Storage Engine

The storage engine is responsible for managing how data is physically stored, retrieved, and maintained. It directly interacts with the file system to perform low-level operations.

Key Responsibilities

  • Organizes data into pages, blocks, and files.
  • Manages indexing for faster data retrieval.
  • Supports various storage formats, such as row-based or columnar storage.
  • InnoDB: Used in MySQL, supports ACID properties.
  • WiredTiger: Used in MongoDB for document-based storage.

3. Transaction Manager

The transaction manager ensures that database transactions adhere to ACID properties (Atomicity, Consistency, Isolation, Durability).

Functions

Image
  • Starts, commits, or rolls back transactions.
  • Monitors concurrency to avoid conflicts.

Example

When transferring funds between bank accounts, the transaction manager ensures that either both the debit and credit operations succeed, or neither does.

4. Lock Manager

The lock manager handles concurrency by ensuring multiple users can access the database simultaneously without conflicts.

Types of Locks

  • Shared Lock: Allows multiple users to read a resource.
  • Exclusive Lock: Prevents others from accessing the resource until the operation is completed.

Role in Concurrency Control

Locks are essential to prevent issues like dirty reads, lost updates, and deadlocks.

5. Cache Manager

The cache manager speeds up query execution by storing frequently accessed data in memory.

How It Works

  • Temporarily stores query results or indexed data.
  • Reduces disk I/O operations, enhancing performance.

Example

A cache manager might store frequently queried customer data in memory to avoid repeated database access.

6. Recovery Manager

The recovery manager ensures the database can be restored to a consistent state after failures, such as crashes or hardware issues.

Functions

  • Maintains logs of all transactions.
  • Performs rollback or roll-forward operations during recovery.

Example

If a transaction fails halfway through due to a power outage, the recovery manager ensures incomplete changes are rolled back.

7. Catalog

The catalog, also known as the data dictionary, is a metadata repository that stores information about the database.

Contents

  • Schema definitions (tables, views, indexes).
  • User permissions and roles.
  • Storage details (size, locations).

Importance

The catalog helps query processors and administrators understand the database's structure and configuration.

The core components of a database system, such as the query processor, storage engine, transaction manager, and cache manager, work together to provide efficient data management and ensure reliability. These components form the backbone of a database, enabling it to process queries, manage data storage, and handle failures effectively.

Overview of Databases
Database Architectures
Mark as Completed

On this page

Core Functional Components

  1. Query Processor
  1. Storage Engine
  1. Transaction Manager
  1. Lock Manager
  1. Cache Manager
  1. Recovery Manager
  1. Catalog